﻿

CREATE PROCEDURE [dbo].[InchideAnExBug]  
@pAn as int,
@codUnit as smallint AS

BEGIN

---creaza tabelele corespunzatoare
EXEC CreazaArhivaExBug @An = @pAn
DECLARE @strAn varchar(4)
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)

SET @strAn=CAST(@pAn AS varchar(4))
SET @strCodUnitate =  CAST(@codUnit AS varchar(20))




BEGIN TRAN 

PRINT('Ordonantari')
--Ordonantari---------------------------------------
SET @Comanda = 'DELETE FROM [Ordonantari'+@strAn+']  WHERE [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari'+@strAn+'] ON;
	INSERT INTO [Ordonantari'+@strAn+'] ([NrDoc]
      ,[DataDoc],[IDFurnizor],[ValoareLei],[IDJurnal],[Scop],[DocJust],[AngLegal],[Achitat],[IdOrdonantare],[CodUnitate]
      ,[IdModPlata],[TotalDatorat],[TotalAvansuri],[ArtAl],[VizatCFP],[MotivRefuz],[IDResponsabil],[DataVizaCFP],[NrRegCFP],[IBANSursa]
      ,[IBANDest],[IDDocumentPlata]) SELECT [NrDoc]
      ,[DataDoc],[IDFurnizor],[ValoareLei],[IDJurnal],[Scop],[DocJust],[AngLegal],[Achitat],[IdOrdonantare],[CodUnitate]
      ,[IdModPlata],[TotalDatorat],[TotalAvansuri],[ArtAl],[VizatCFP],[MotivRefuz],[IDResponsabil],[DataVizaCFP],[NrRegCFP],[IBANSursa]
      ,[IBANDest],[IDDocumentPlata] FROM [Ordonantari] WHERE YEAR(DataDoc)='+@strAn +' AND [CodUnitate]='+@strCodUnitate +
      ';SET IDENTITY_INSERT [Ordonantari'+@strAn+'] OFF'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'UPDATE [Ordonantari] SET [IDDocumentPlata]=null  WHERE YEAR(DataDoc)='+@strAn +' AND [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'UPDATE [DocumentePlata] SET [IDOrdonantare]=null  WHERE  EXISTS(SELECT * FROM Ordonantari o  WHERE o.IDOrdonantare=DocumentePlata.IDOrdonantare AND  YEAR(DataDoc)='+@strAn +' AND [CodUnitate]=' + @strCodUnitate + ')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE


PRINT('Sterge arhivat Ordonantari')
SET @Comanda = 'DELETE FROM [Ordonantari] WHERE YEAR(DataDoc)='+@strAn +' AND [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'UPDATE [ExBugSetup] SET [AnulCurent] = [AnulCurent] + 1 WHERE [CodUnitate] =' + @strCodUnitate 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	


COMMIT


GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la inchiderea anului!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

END